Everything about Visual Basic For Applications totally explained
Visual Basic for Applications (VBA) is an implementation of Microsoft'sVisual Basic, an event driven programming language and associated integrated development environment (IDE) which is built into most Microsoft Office applications. By embedding the VBA IDE into their applications, developers can build custom solutions using Microsoft Visual Basic. It was also built into Office applications up to version 2004 for Apple'sMac OS X, other Microsoft applications such as Microsoft MapPoint and Microsoft Visio; as well as being at least partially implemented in some other applications such as AutoCAD, WordPerfect and ArcGIS. It supersedes and expands on the capabilities of earlier application-specific macro programming languages such as Word's WordBasic, and can be used to control almost all aspects of the host application, including manipulating user interface features, such as menus and toolbars, and working with custom user forms or dialog boxes. VBA can also be used to create import and export filters for various file formats, such as ODF.
As its name suggests, VBA is closely related to Visual Basic, but one can normally run this code within a host application rather than as a standalone application. It can, however, be used to control one application from another using OLE Automation. For example, it's used to automatically create a Word report from Excel data, in turn automatically collected by Excel from polled observation sensors.
VBA is functionally rich and extremely flexible but it does have some important limitations, including limited support for function pointers which are used as callback functions in the Windows API. It has the ability to use (but not create) (ActiveX/COM) DLLs, and later versions add support for class modules.
Language
Code written in VBA is compiled to a proprietary intermediate language called P-code (packed code), which is stored by the hosting applications (Access, Excel, Word) as a separate stream in Structured storage files (for example, .doc or .xls) independent of the document streams. The intermediate code is then interpreted This portion of VBA is called the Object Model for the application. A map of the object model is online for Excel and for Word. Much of the difficulty in using VBA is related to learning the object model, which uses names invented by the originators of the model that may be less than transparent to a new user. One way to learn the terms and syntax of the object model is to use the macro recorder to record the steps taken to achieve a desired result using the mouse and menus of the application. Once this is done, the VBA code constructed by the recorder can be viewed in the VBA editor, and often greatly streamlined or generalized with only a modicum of understanding of VBA itself. Unfortunately, the macro recorder doesn't always record everything (particularly for graphs). Use of debugging tools to discover VBA constructs for some cases where the macro recorder doesn't work are described by Jelen and Syrstad, but some steps may remain obscure.
Automation
Interaction with the host application uses OLE Automation. Typically, the host application provides a type library and API documentation which document how VBA programs can interact with the application. This documentation can be examined from inside the VBA development environment using its Object Browser.
VBA programs which are written to use the OLE Automation interface of one application can't be used to automate a different application, even if that application hosts the Visual Basic runtime, because the OLE Automation interfaces will be different. For example, a VBA program written to automate Microsoft Word can't be used with a different word processor, even if that word processor hosts VBA.
Conversely, multiple applications can be automated from the one host by creating Application objects within the VBA code. References to the different libraries must be created within the VBA client before any of the methods, objects, etc. become available to use in the application. These application objects create the OLE link to the application when they're first created. Commands to the different applications must be done explicitly through these application objects in order to work correctly.
For example: In Microsoft Access, users automatically have access to the Access library. References to the Excel, Word, and Microsoft Outlook libraries can also be created. This will allow creating an application that runs a query in Access, exports the results to Excel, formats the text, then writes a Mail merge document in Word that it automatically e-mails to each member of the original query through Outlook. (In this example, it's important to note that Microsoft Outlook contains a security feature that forces a user to allow, disallow, or cancel an e-mail being sent through an automated process with a forced 5 second wait. Information on this can be found at the Microsoft website.)
VBA programs can be attached to a menu button, a macro, a keyboard shortcut, or an OLE/COM event, such as the opening of a document in the application. The language also provides a user interface in the form of UserForms, which can host ActiveX controls for added functionality.
Security concerns
Like any common programming language, VBA macros can be created with a malicious intent. Using VBA, most of the security features lie in the hands of the user, not the author. The VBA 'host-application' options are accessible to the user. The user who runs any document containing VBA macros can preset the software with user preferences, much like those for internet browsers. End-users can protect themselves from attack by disabling macros from running in an application if they don't intend to use documents containing them, or only grant permission for a document to run VBA code if they're sure the source of the document can be trusted.
Examples
A common use of VBA is to add functionality that may be missing from the standard user interface. This macro provides a shortcut for entering the current date in Word:
Sub EnterCurrentDate
' EnterCurrentDate Macro
' Macro recorded 15/03/2005 by UserName
'
Selection.InsertDateTime DateTimeFormat:="dd-MM-yy", InsertAsField:=False, _
DateLanguage:=wdEnglishAUS, CalendarType:=wdCalendarWestern, _
InsertAsFullWidth:=False
End Sub
VBA is useful for automating database tasks such as traversing a table:
Sub LoopTableExample
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM tblMain")
Do Until rs.EOF
MsgBox rs!FieldName
rs.MoveNext
Loop
rs.Close
Set db = Nothing
End Sub
VBA is useful for automating repeated actions in rows of a spreadsheet. For example, using the following code example, the built-in iterative solver Goal Seek is applied automatically to each row in a column array, avoiding repeated use of manual menu entry. Below a column variable "C_M" determines the values of another column variable "Target" in some nonlinear fashion. The built-in nonlinear solver Goal Seek is called to find the value of "C_M" that brings "Target" to value one. The subroutine is inserted into the workbook using the VBA editor and command Insert Module. It is called directly from the VBA editor, or by using a "hot key" or keyboard shortcut. Values on the spreadsheet automatically update as the rows are scanned.
It is useful to note that subroutines have the power to update variables on the spreadsheet; functions don't - they simply report their evaluation.
Line Option Explicit isn't part of the subroutine: it sets a compiler option that forces identification of all variables that have not been specified in Dim statements, which avoids possible knotty debugging problems that can arise due to typos. Notation (' ) in the following code denotes a comment, and (_) line continuation. The code uses NAMED variables: a form of cell reference in which cells are assigned names of user choice, rather than the standard cell designation referring to specific row and column numbers. Naming is accomplished on the worksheet using the Excel "Name Manager", or menu Insert Name: Create.
Option Explicit
Sub SetTarget
'
' SetTarget Macro
'
Dim J As Integer
Dim Size As Integer
'
' On the spreadsheet, array "C_M" is a NAMED column variable
' Its members use a row index taken as J
' Built-in function COUNT determines size of array "C_M"
'
Size = Range("C_M").Cells.Count
'
' Set initial value of all members of array
' C_M to 1E-06; J = row index
'
For J = 1 To Size
Range("C_M").Cells(J) = 0.000001
Next J
'
' "Target" is another NAMED array on the spreadsheet of
' dimension "Size"; the same size as array "C_M"
'
' Each "Target" entry in each row depends in a
' specified way upon the value of "C_M" in that row,
' for example, by a function such as: Target = C_M*C_M
'
' GOAL SEEK is a built-in iterative solver in Excel
'
' Call GOAL SEEK to set each "Target" member to unity: for example,
' taking J = row index, in row J the cell named "C_M" is changed
' by GOAL SEEK until "Target" in row J is one
'
' Syntax (aside from "for-next" details) found with macro recorder;
' underscore "_" is line continuation
'
For J = 1 To Size
Range("Target").Cells(J).GoalSeek Goal:=1, _
ChangingCell:=Range("C_M").Cells(J)
Next J
End Sub
VBA can be used to create a user defined function (UDF) for use in a Microsoft Excel workbook:
Public Function BusinessDayPrior(dt As Date) As Date
Select Case Weekday(dt, vbMonday)
Case 1
BusinessDayPrior = dt - 3 'Monday becomes Friday
Case 7
BusinessDayPrior = dt - 2 'Sunday becomes Friday
Case Else
BusinessDayPrior = dt - 1 'All other days become previous day
End Select
End Function
Example of how to add an external application object (The user must have the application library referenced in the application before this):
Public Sub Example
Dim XLApp As Excel.Application
Dim WDApp As Word.Application
Set XLApp = CreateObject("Excel.Application")
Set WDApp = createObject("Word.Application")
' ...your code here...
XLApp.Quit
WDApp.Quit
Set XLApp = Nothing
Set WDApp = Nothing
End Sub
Future
As of July 1, 2007, Microsoft no longer offers VBA distribution licenses to new customers. Microsoft intend to replace VBA with .NET-based languages ever since the release of the .NET Framework. The .NET Framework versions 1.0 and 1.1 included a scripting runtime technology known as Script for the .NET Framework . Also, Visual Studio .NET 2002 and 2003 SDK contained a separate scripting IDE called Visual Studio for Applications (VSA) that supported VB.NET . One of its significant features was that the interfaces to the technology were also available via Active Scripting (VBScript and JScript), allowing even .NET-unaware applications to be scripted using .NET languages. However, VSA was deprecated in version 2.0 of the .NET Framework, leaving no clear upgrade path for applications desiring Active Scripting support (although "scripts" can be created in C#, VBScript, and other .NET languages, which can be compiled and executed at run-time via libraries installed as part of the standard .NET runtime).
Support for VBA in the Mac OS X version of Microsoft Office was dropped with the release of Microsoft Office 2008 for Mac. . The official reason given was that VBA relied heavily on machine code written for the PowerPC architecture, and that rewriting this code for dual PowerPC/Intel architectures would have added another 2 years to the development of the suite. However, the office suite can to an extent be automated using AppleScript. In a press statment released on May 13, 2008, Microsoft's Macintosh Business Unit (Mac BU) announced that VBA will be returning in the next version of Office for Mac. Microsoft has also clearly stated that they've no plans to remove VBA from the Windows version of Office.
Visual Studio Tools for Applications (VSTA)
With the release of Visual Studio 2005, Microsoft announced Visual Studio Tools for Applications (VSTA), an application customization toolkit based on the .NET Framework 2.0 and built on the same architecture as Visual Studio Tools for Office (VSTO). Some of the technology developed for VSA was incorporated within VSTA. VSTA consists of an SDK and a customized developer IDE, based on the Visual Studio 2005 IDE, and a runtime that can be embedded in applications to expose its features via the .NET object model. It also includes an end-user IDE incorporating Visual Basic .NET and C#. VSTA also features 64-bit support, macro recoding and other usual Visual Studio 2005 IDE features, but doesn't incorporate Active Scripting support.
The first CTP was released in April 2006 and version 1.0 was released to manufacturing along with Office 2007 . It is included with Office 2007, and the SDK is available separately. VSTA is licensed from Microsoft depending on the usage scenarios for redistribution with applications. Office 2007 applications continue to integrate with VBA, except for InfoPath 2007 which integrates with VSTA.
The next version of VSTA, based on Visual Studio 2008 will be released in mid-2008 . The second version of VSTA will be significantly different from the first version, including features such as dynamic programing and support for WPF, WCF, WF, LINQ, and .NET 3.5. A beta release is currently available, titled Visual Studio Tools For Applications 2.0 .
Do you know how hard it is to get a link from a large encyclopaedia? Well we're different and will prove it. To get a link from us just add the following HTML to your site on a relevant page:
<a href="http://visual_basic_for_applications.totallyexplained.com">Visual Basic for Applications Totally Explained</a>
Then simply click through this link from your web page. Our crawlers will verify your link, extract the title of your web page and instantly add a link back to it. If you like you can remove the words Totally Explained and embed the link in article text.
As long as your link remains in place, we'll keep our link to you right here. Please play fair - our crawlers are watching. Your site must be closely related to this one's topic. Any kind of spamming, dubious practises or removing the link will result in your link from us being dropped and, potentially, your whole site being banned.
